// 修复文件路径和PDF转换记录
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');

// 数据库连接配置
const pool = new Pool({
  host: '118.178.138.0',
  port: 9070,
  database: 'postgres',
  user: 'postgres',
  password: 'difyai123456',
  max: 20,
  idleTimeoutMillis: 90900,
  connectionTimeoutMillis: 2000,
});

async function fixFilePaths() {
  try {
    console.log('🔧 开始修复文件路径和PDF转换记录...\n');
    
    // 获取uploads目录中的实际文件
    const uploadsDir = path.join(process.cwd(), 'uploads');
    const actualFiles = fs.readdirSync(uploadsDir);
    console.log('📁 实际存在的文件:');
    actualFiles.forEach((file, index) => {
      console.log(`  ${index + 1}. ${file}`);
    });
    
    // 查询数据库中的媒体文件
    const mediaFilesQuery = `
      SELECT id, user_id, original_name, file_name, file_url, mime_type
      FROM media_files 
      ORDER BY created_at DESC
    `;
    
    const mediaFilesResult = await pool.query(mediaFilesQuery);
    console.log(`\n📋 数据库中的媒体文件记录 (${mediaFilesResult.rows.length}条):`);
    
    let fixedCount = 0;
    
    for (const dbFile of mediaFilesResult.rows) {
      console.log(`\n检查文件: ${dbFile.original_name} (ID: ${dbFile.id})`);
      console.log(`  数据库记录的文件名: ${dbFile.file_name}`);
      
      const dbFilePath = path.join(uploadsDir, dbFile.file_name);
      const fileExists = fs.existsSync(dbFilePath);
      
      console.log(`  文件是否存在: ${fileExists ? '✅' : '❌'}`);
      
      if (!fileExists) {
        // 尝试根据原始文件名匹配实际文件
        const matchingFiles = actualFiles.filter(actualFile => {
          // 检查是否包含相似的名称或扩展名
          const dbExt = path.extname(dbFile.original_name).toLowerCase();
          const actualExt = path.extname(actualFile).toLowerCase();
          
          return actualExt === dbExt || actualFile.includes(dbFile.original_name.split('.')[0]);
        });
        
        if (matchingFiles.length > 0) {
          const matchedFile = matchingFiles[0];
          console.log(`  找到匹配文件: ${matchedFile}`);
          
          // 更新数据库记录
          const updateQuery = `
            UPDATE media_files 
            SET file_name = $1, file_url = $2
            WHERE id = $3
          `;
          
          await pool.query(updateQuery, [
            matchedFile,
            `/uploads/${matchedFile}`,
            dbFile.id
          ]);
          
          console.log(`  ✅ 已更新数据库记录`);
          fixedCount++;
        } else {
          console.log(`  ⚠️ 未找到匹配的实际文件`);
        }
      }
      
      // 更新PDF转换记录
      if (dbFile.mime_type === 'application/pdf') {
        const pdfConversionQuery = `
          UPDATE pdf_conversions 
          SET conversion_status = 'completed', pdf_url = $1, pdf_path = $2
          WHERE original_file_id = $3
        `;
        
        const updatedFilePath = path.join(uploadsDir, dbFile.file_name);
        if (fs.existsSync(updatedFilePath)) {
          await pool.query(pdfConversionQuery, [
            `/uploads/${dbFile.file_name}`,
            updatedFilePath,
            dbFile.id
          ]);
          console.log(`  📄 已更新PDF转换记录为completed`);
        }
      }
    }
    
    console.log(`\n📊 修复结果:`);
    console.log(`  - 修复文件数: ${fixedCount}`);
    
    // 显示最新的PDF转换记录统计
    const statsQuery = `
      SELECT conversion_status, COUNT(*) as count
      FROM pdf_conversions
      GROUP BY conversion_status
      ORDER BY conversion_status
    `;
    
    const statsResult = await pool.query(statsQuery);
    console.log(`\n📈 PDF转换记录统计:`);
    statsResult.rows.forEach(row => {
      console.log(`  - ${row.conversion_status}: ${row.count}条`);
    });
    
    console.log('\n✅ 修复完成！');
    
  } catch (error) {
    console.error('❌ 修复失败:', error);
  } finally {
    await pool.end();
  }
}

// 运行修复
if (require.main === module) {
  fixFilePaths();
}

module.exports = { fixFilePaths };